﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Server;



/*================================================================================================
Name:			ClrDDL
Type:			CLR Class
Owner:					
Description:	DDL class   
Parameters:		   
Dependens on:  	
Usage:			
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			
Version:		20121030
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/30/2012 - Adrian E Dudau - Function fn_object_filegroup created
================================================================================================*/
public partial class ClrDDL
{

    //----------------------------------------------------------------------------------------------------------------------
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read )]
    public static SqlString fn_object_filegroup(SqlString object_name, SqlString object_type, SqlString object_parent)
    {
        String filegroup_name = null;
        String object_db = String.Empty; 

        SqlConnection SqlConn = new SqlConnection("context connection=true");
        using (SqlCommand SqlCmd = new SqlCommand())
        {
            SqlConn.Open();
            SqlCmd.Connection = SqlConn;
            object_db = SqlConn.Database; 

            // for indexes
            if (object_type.ToString().Trim().ToUpper() == "I")
            {
                SqlCmd.CommandText = " SELECT TOP 1 'fg_name' = NULLIF(fg_name,'') FROM ( "
                                   + " SELECT 'fg_name' = NULL UNION "  
                                   + " SELECT DISTINCT 'fg_name' = fg.name "
                                   + " FROM [" + object_db.ToString() + "].[sys].[indexes] idx "
                                   + " INNER JOIN [" + object_db.ToString() + "].[sys].[filegroups] fg "
                                   + "     ON idx.data_space_id = fg.data_space_id "
                                   + " WHERE idx.[name] = '" + object_name.ToString() + "' "
                                   + "     AND idx.[object_id] = OBJECT_ID('" + object_parent.ToString() + "') " 
                                   + " ) DTBL ORDER BY 1 DESC ";
            }

            else // for other object types
            {
                SqlCmd.CommandText = " SELECT TOP 1 'fg_name' = NULLIF(fg_name,'') FROM ( "
                                   + " SELECT 'fg_name' = NULL UNION "  
                                   + " SELECT DISTINCT 'fg_name' = fg.name "
                                   + " FROM [" + object_db.ToString() + "].[sys].[all_objects] obj "
                                   + " INNER JOIN [" + object_db.ToString() + "].[sys].[schemas] sch "
                                   + "     ON obj.[schema_id] = sch.[schema_id] "
                                   + " INNER JOIN [" + object_db.ToString() + "].[sys].[indexes] idx "
                                   + "     ON obj.[object_id] = idx.[object_id] "
                                   + " INNER JOIN [" + object_db.ToString() + "].[sys].[filegroups] fg "
                                   + "     ON idx.data_space_id = fg.data_space_id "
                                   + " WHERE idx.[type] IN(0,1)	"
                                   + "     AND sch.[name] = ISNULL('" + object_parent.ToString() + "', 'dbo') "
                                   + "     AND obj.[name] = '" + object_name.ToString() + "' "
                                   + " ) DTBL ORDER BY 1 DESC ";
            } 


            filegroup_name = SqlCmd.ExecuteScalar().ToString();
            //filegroup_name = SqlCmd.CommandText;
        }

        if (filegroup_name.Trim() == String.Empty)
            return null;
        else 
            return new SqlString(filegroup_name);
    } //end fn_object_filegroup
}


// TO DO
// fn_object_db(object_id) -> return table with all matches in all databases (db_name, object_name, object_parent/schema, object_type)
// fn_win_eventlog
// fn_file_exists
// fn_win_schtasks
// fn_win_hostname
// fn_net_user, etc
// fn_net_ipconfig
// fn_wmi_select


//[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
//public static SqlBoolean fn_object_exists(SqlString object_name, SqlString object_parent, SqlString object_db)
//{
//    // declare/initialize variables 
//    //String db_name = String.Empty;

//    //SqlConnection SqlConn = new SqlConnection("context connection=true");
//    //using (SqlCommand SqlCmd = new SqlCommand())
//    //{
//    //    SqlConn.Open();
//    //    SqlCmd.Connection = SqlConn;
//    //    SqlCmd.CommandText = " SELECT 'result' = DB_NAME(" + database_id.ToString() + ")";
//    //    db_name = SqlCmd.ExecuteScalar().ToString();
//    //}

//    return false;
//}
//// end fn_object_exists


//[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
//public static SqlString fn_object_drop(SqlInt64 object_id)
//{
//    // declare/initialize variables 
//    String db_name = String.Empty;

//    //SqlConnection SqlConn = new SqlConnection("context connection=true");
//    //using (SqlCommand SqlCmd = new SqlCommand())
//    //{
//    //    SqlConn.Open();
//    //    SqlCmd.Connection = SqlConn;
//    //    SqlCmd.CommandText = " SELECT 'result' = DB_NAME(" + database_id.ToString() + ")";
//    //    db_name = SqlCmd.ExecuteScalar().ToString();
//    //}

//    return db_name;
//}
//// end fn_object_drop

//[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
//public static SqlString fn_object_type(SqlInt64 object_id)
//{
//    // declare/initialize variables 
//    //String db_name = String.Empty;

//    //SqlConnection SqlConn = new SqlConnection("context connection=true");
//    //using (SqlCommand SqlCmd = new SqlCommand())
//    //{
//    //    SqlConn.Open();
//    //    SqlCmd.Connection = SqlConn;
//    //    SqlCmd.CommandText = " SELECT 'result' = DB_NAME(" + database_id.ToString() + ")";
//    //    db_name = SqlCmd.ExecuteScalar().ToString();
//    //}

//    return db_name;
//}
//// end fn_object_type

    
//    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
//    public static SqlString fn_object_dbname(SqlInt64 object_id)
//    {
//        // declare/initialize variables 
//        String db_name = String.Empty;

//        //SqlConnection SqlConn = new SqlConnection("context connection=true");
//        //using (SqlCommand SqlCmd = new SqlCommand())
//        //{
//        //    SqlConn.Open();
//        //    SqlCmd.Connection = SqlConn;
//        //    SqlCmd.CommandText = " SELECT 'result' = DB_NAME(" + database_id.ToString() + ")";
//        //    db_name = SqlCmd.ExecuteScalar().ToString();
//        //}

//        return db_name;
//    }
//    // end fn_object_dbname

    

//    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
//    public static SqlString fn_object_definition(SqlInt64 object_id, SqlInt64 database_id)
//    {
//        // declare/initialize variables 
//        String obj_db = String.Empty;
//        String obj_schema = String.Empty;
//        String obj_name = String.Empty;
//        String obj_type = String.Empty;
//        String obj_def = String.Empty; 

//        //SqlConnection SqlConn = new SqlConnection("context connection=true");
//        //using (SqlCommand SqlCmd = new SqlCommand())
//        //{
//        //    SqlConn.Open();
//        //    SqlCmd.Connection = SqlConn;
//        //    SqlCmd.CommandText = " SELECT 'result' = DB_NAME(" + database_id.ToString() + ")";
//        //    obj_db = SqlCmd.ExecuteScalar().ToString();


//        //    // if invalid db id exit
//        //    if (database_id <= 0 || obj_db == null)
//        //    {
//        //        return null;
//        //    } /*end if (database_id <= 0 || obj_db == null)*/


//        //    // check if native SQL function OBJECT_DEFINITION works
//        //    SqlCmd.CommandText = "SELECT 'result' = OBJECT_DEFINITION(" + object_id.ToString() + ");";
//        //    obj_def = SqlCmd.ExecuteScalar().ToString();

//        //    //check if OBJECT_DEFINITION retuned null
//        //    if (obj_def == null)
//        //    {
//        //        SqlCmd.CommandText = "SELECT 'result' = [schema] FROM " + obj_db.ToString() + ".sys.all_objects WHERE id = " + object_id.ToString();
//        //        obj_schema = SqlCmd.ExecuteScalar().ToString();
//        //    }


//        //    //SqlCmd.CommandText = "SELECT 'result' = [schema] FROM " + obj_db.ToString() + ".sys.all_objects WHERE id = " + object_id.ToString();
//        //    //obj_schema = SqlCmd.ExecuteScalar().ToString();

//        //    //SqlCmd.CommandText = "SELECT 'result' = [name] FROM " + obj_db.ToString() + ".sys.all_objects WHERE id = " + object_id.ToString();
//        //    //obj_name = SqlCmd.ExecuteScalar().ToString();

//            return obj_def;
//        }
//    }
//    // end fn_object_definition


////[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
////public static SqlString fn_table_definition(SqlString table_name, SqlString table_schema, SqlString table_db, SqlString table_filegroup)
////{
////    return "";
////}

//};


